package com.travel.dao.impl;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.travel.dao.ScenicFavoriteDao;
import com.travel.entity.Scenic;
import com.travel.entity.ScenicFavorite;
import com.travel.entity.User;
import com.travel.util.DBUtils;
import com.travel.util.PageInfo;

/**
* @Title ScenicFavoriteDaoImpl.java
* @Package com.travel.dao.impl
* @Author zengbochao
* @Time 2020年10月29日 下午3:24:49
* @Version 1.0
* @Description: TODO
*/
public class ScenicFavoriteDaoImpl implements ScenicFavoriteDao {
	
	private DBUtils db=new DBUtils();
	
	@Override
	public PageInfo<ScenicFavorite> findByPage(int pageNum, int pageSize, int userId) {
		List<ScenicFavorite> data=new ArrayList<ScenicFavorite>();
		StringBuffer sb=new StringBuffer();
		sb.append("select")
			.append(" F.favorite_id,")
			.append(" F.user_id,")
			.append(" F.scenic_id,")
			.append(" U.user_name,")
			.append(" S.scenic_id,")
			.append(" S.scenic_name,")
			.append(" S.scenic_picture,")
			.append(" S.scenic_desc,")
			.append(" S.scenic_price")
			.append(" FROM tab_scenic_favorite F")
			.append(" JOIN tab_user U")
			.append(" ON U.user_id=F.user_id")
			.append(" JOIN tab_scenic S")
			.append(" ON F.scenic_id=S.scenic_id")
			.append(" where U.user_id=?")
			.append(" limit ?,?");
		
		ResultSet rs=db.query(sb.toString(), userId,(pageNum-1)*pageSize, pageSize);
		try {
			while (rs.next()) {
				Integer favoriteId=rs.getInt("favorite_id");
				Integer userId1=rs.getInt("user_id");
				Integer scenicId=rs.getInt("scenic_id");
				String  userName = rs.getString("user_name");
				String  scenicName = rs.getString("scenic_name");
				String  scenicPicture=rs.getString("scenic_picture");
				BigDecimal scenicPrice=rs.getBigDecimal("scenic_price");
				String scenicDesc=rs.getString("scenic_desc");
				ScenicFavorite scenicFavorite=new ScenicFavorite(favoriteId,userId1,scenicId,new User(null,userName,null,null,null,null,null,null),new Scenic(scenicId,scenicName,scenicDesc,scenicPicture,scenicPrice));
				data.add(scenicFavorite);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// 2.获取总记录数(未分页之前)
			int total = 0;
			sb = new StringBuffer();
			sb.append("select")
			  .append(" count(*)")
			  .append(" from tab_scenic_favorite ")
			  .append(" where user_id=?");
			
			rs  = db.query(sb.toString(),userId);
			// 遍历结果集
			try {
				while (rs.next()) {
					total  = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			// 构建分页对象
			PageInfo<ScenicFavorite> pageInfo = new PageInfo<ScenicFavorite>(data, pageNum, pageSize, total);
			
			return pageInfo;
	}

	@Override
	public int insert(ScenicFavorite scenicFavorite) {
		// TODO Auto-generated method stub
		return db.update("INSERT INTO tab_scenic_favorite(favorite_id,user_id,scenic_id) VALUES(null,?,?)",scenicFavorite.getUserId(),scenicFavorite.getScenicId() );
	}

	@Override
	public int deleteById(int scenicId) {
		// TODO Auto-generated method stub
		return db.update("delete from tab_scenic_favorite where favorite_id=?", scenicId);
	}

	@Override
	public List<ScenicFavorite> findbyUserId(int userId, int scenicId) {
		List<ScenicFavorite> list=new ArrayList<ScenicFavorite>();
		StringBuffer sb=new StringBuffer();
		sb.append("select favorite_id,user_id,scenic_id from tab_scenic_favorite where user_id=? AND scenic_id=?");
		ResultSet rs=db.query(sb.toString(), userId,scenicId);
		try {
			while (rs.next()) {
				Integer favoriteId=rs.getInt("favorite_id");
				Integer userId1=rs.getInt("user_id");
				Integer scenicId1=rs.getInt("scenic_id");
				ScenicFavorite scenicFavorite=new ScenicFavorite(favoriteId, userId1, scenicId1);
				list.add(scenicFavorite);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;

	}

}
